﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using fetco.entities;

namespace fetco.data
{
    public class WebUserDAO
    {
        private static WebUserDAO _instance;
        private Connector _conn;

        public WebUserDAO()
        {
            this._conn = Connector.GetInstance();
        }
        public static WebUserDAO GetInstance()
        {
            if (_instance == null)
            {
                _instance = new WebUserDAO();
            }
            return _instance;
        }

        #region Create WebUser
        private WebUser CreateWebUser(SqlDataReader reader)
        {
            WebUser webuser = new WebUser();
            
            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.UserId.ToString())))
                webuser.UserId = (long)reader[WebUserField.UserId.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.LoginName.ToString())))
                webuser.LoginName = (string)reader[WebUserField.LoginName.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.FirstName.ToString())))
                webuser.FirstName = (string)reader[WebUserField.FirstName.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.LastName.ToString())))
                webuser.LastName = (string)reader[WebUserField.LastName.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.Password.ToString())))
                webuser.Password = (string)reader[WebUserField.Password.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.Avatar.ToString())))
                webuser.Avatar = (string)reader[WebUserField.Avatar.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.Birthday.ToString())))
                webuser.Birthday = (long)reader[WebUserField.Birthday.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.Gender.ToString())))
                webuser.Gender = (int)reader[WebUserField.Gender.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.Address.ToString())))
                webuser.Address = (string)reader[WebUserField.Address.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.CreateDate.ToString())))
                webuser.CreateDate = (long)reader[WebUserField.CreateDate.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.Published.ToString())))
                webuser.Published = (int)reader[WebUserField.Published.ToString()];

            if (!reader.IsDBNull(reader.GetOrdinal(WebUserField.Status.ToString())))
                webuser.Status = (int)reader[WebUserField.Status.ToString()];

            return webuser;
        }
        private WebUser CreateWebUser(SqlDataReader reader, int numfield)
        {
            WebUser webuser = new WebUser();
            for (int index = 0; index < numfield; index++)
            {
                if (!reader.IsDBNull(index))
                {
                    switch (reader.GetName(index))
                    {
                        case "UserId":
                            {
                                webuser.UserId = (long)reader[index];
                                break;
                            }
                        case "LoginName":
                            {
                                webuser.LoginName = (string)reader[index];
                                break;
                            }
                        case "FirstName":
                            {
                                webuser.FirstName = (string)reader[index];
                                break;
                            }
                        case "LastName":
                            {
                                webuser.LastName = (string)reader[index];
                                break;
                            }
                        case "Password":
                            {
                                webuser.Password = (string)reader[index];
                                break;
                            }
                        case "Avatar":
                            {
                                webuser.Avatar = (string)reader[index];
                                break;
                            }
                        case "Birthday":
                            {
                                webuser.Birthday = (long)reader[index];
                                break;
                            }
                        case "Gender":
                            {
                                webuser.Gender = (int)reader[index];
                                break;
                            }
                        case "Address":
                            {
                                webuser.Address = (string)reader[index];
                                break;
                            }
                        case "CreateDate":
                            {
                                webuser.CreateDate = (long)reader[index];
                                break;
                            }
                        case "Published":
                            {
                                webuser.Published = (int)reader[index];
                                break;
                            }
                        case "Status":
                            {
                                webuser.Status = (int)reader[index];
                                break;
                            }
                        default:
                            break;
                    }
                }                   
            }
            return webuser;
        }
        #endregion
        
        #region Get Data
        public WebUser GetWebUserBy(long user_id)
        {
            WebUser webuser = null;
            string output = WebUserField.UserId.ToString() + "," +
                            WebUserField.LoginName.ToString() + "," +
                            WebUserField.FirstName.ToString() + "," +
                            WebUserField.LastName.ToString() + "," +
                            WebUserField.Avatar.ToString() + "," +
                            WebUserField.Birthday.ToString() + "," +
                            WebUserField.Gender.ToString() + "," +
                            WebUserField.Address.ToString() + "," +
                            WebUserField.CreateDate.ToString() + "," +
                            WebUserField.Published.ToString() + "," +
                            WebUserField.Status.ToString();
            string condition = WebUserField.UserId.ToString() + "=" + user_id;
            string query = @"select " + output + " from " + WebUser.TableName + " where " + condition;
            SqlDataReader reader = this._conn.ExcuteReader(query);
            if(reader!=null && reader.Read())
            {
                int numfield = reader.FieldCount;
                webuser = this.CreateWebUser(reader,numfield);
            }
            return webuser;
        }
        public WebUser GetWebUserBy(string login_name)
        {
            WebUser webuser = null;
            string output = WebUserField.UserId.ToString() + "," +
                            WebUserField.Password.ToString();
            
            string condition = WebUserField.LoginName.ToString() + "='" + login_name + "'";
            condition += " and " + WebUserField.Published.ToString() + "=1";

            string query = @"select " + output + " from " + WebUser.TableName + " where " + condition;
            SqlDataReader reader = this._conn.ExcuteReader(query);
            if (reader != null && reader.Read())
            {
                int numfield = reader.FieldCount;
                webuser = this.CreateWebUser(reader, numfield);
            }
            return webuser;
        }
        
        public ListWebUser GetListWebUser(string fieldname,int currpage,int pagesize,ref long totalrecord,OrderByDirection order)
        {
            ListWebUser listwebuser = new ListWebUser();
            string condition="1";
            totalrecord = this._conn.GetTotalRecord(WebUser.TableName, condition);
            string query = this._conn.PagingQuery(WebUser.TableName, fieldname, condition, WebUserField.CreateDate.ToString(), order, currpage, pagesize, totalrecord);
            SqlDataReader reader = this._conn.ExcuteReader(query);
            if (reader != null)
            {
                int numfield = reader.FieldCount;
                while (reader.Read())
                {
                    WebUser webuser = this.CreateWebUser(reader, numfield);
                    listwebuser.Add(webuser);
                }
            }
            return listwebuser;
        }
        public ListWebUser GetListWebUserById(string list_user_id,string fieldname)
        {
            ListWebUser listwebuser = new ListWebUser();
            string condition = WebUserField.UserId.ToString() + " in (" + list_user_id + ")";
            string query = @"select " + fieldname + " from " + WebUser.TableName + " where " + condition;
            SqlDataReader reader = this._conn.ExcuteReader(query);
            if (reader != null)
            {
                int numfield = reader.FieldCount;
                while (reader.Read())
                {
                    WebUser webuser = this.CreateWebUser(reader, numfield);
                    listwebuser.Add(webuser);
                }
            }
            return listwebuser;
        }

        public long GetWebUserId(string login_name)
        {
            string output = WebUserField.UserId.ToString();

            string condition = WebUserField.LoginName.ToString() + "='" + login_name + "'";

            string query = @"select " + output + " from " + WebUser.TableName + " where " + condition;
            SqlDataReader reader = this._conn.ExcuteReader(query);
            if (reader != null && reader.Read())
            {
                if (!reader.IsDBNull(0))
                    return (long)reader[0];
            }
            return 0;
        }
        public string GetWebUserPass(long user_id)
        {
            string output = WebUserField.Password.ToString();

            string condition = WebUserField.UserId.ToString() + "=" + user_id;

            string query = @"select " + output + " from " + WebUser.TableName + " where " + condition;
            SqlDataReader reader = this._conn.ExcuteReader(query);
            if (reader != null && reader.Read())
            {
                if (!reader.IsDBNull(0))
                    return (string)reader[0];
            }
            return "";
        }
        #endregion

        #region Set Data
        public void SetWebUser(WebUser webuser)
        {
            string input = WebUserField.LoginName.ToString() + "," +
                           WebUserField.FirstName.ToString() + "," +
                           WebUserField.LastName.ToString() + "," +
                           WebUserField.Password.ToString() + "," +
                           WebUserField.Avatar.ToString() + "," +
                           WebUserField.Birthday.ToString() + "," +
                           WebUserField.Gender.ToString() + "," +
                           WebUserField.Address.ToString() + "," +
                           WebUserField.CreateDate.ToString() + "," +
                           WebUserField.Published.ToString() + "," +
                           WebUserField.Status.ToString();
            string value = "'" + webuser.LoginName + "'," +
                           "'" + webuser.FirstName + "'," +
                           "'" + webuser.LastName + "'," +
                           "'" + webuser.Password + "'," +
                           "'" + webuser.Avatar + "'," +
                                 webuser.Birthday + "," +
                                 webuser.Gender + "," +
                           "'" + webuser.Address + "'," +
                                 webuser.CreateDate + "," +
                                 webuser.Published + "," +
                                 webuser.Status;
            string query = @"insert into " + WebUser.TableName + " (" + input + ") values(" + value + ")";
            if(this._conn.ExcuteNonQuery(query)>0)
            {
                webuser.UserId = (long)this._conn.GetMaxValue(WebUserField.UserId.ToString(),WebUser.TableName);
            }
        }
        #endregion

        #region Delete Data
        public bool DelWebUser(long user_id)
        {
            string condition = WebUserField.UserId + "=" + user_id;
            string query = @"delete from " + WebUser.TableName + " where " + condition;
            if (this._conn.ExcuteNonQuery(query) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region Update Data
        public bool UpdateWebUser(WebUser webuser)
        {
            string value = WebUserField.FirstName.ToString() + "='" + webuser.FirstName + "'," +
                           WebUserField.LastName.ToString() + "='" + webuser.LastName + "'," +
                           WebUserField.Avatar.ToString() + "='" + webuser.Avatar + "'," +
                           WebUserField.Birthday.ToString() + "=" + webuser.Birthday + "," +
                           WebUserField.Gender.ToString() + "=" + webuser.Gender + "," +
                           WebUserField.Address.ToString() + "='" + webuser.Address + "'";
            string condition = WebUserField.UserId.ToString() + "=" + webuser.UserId;
            string query = @"update " + WebUser.TableName + " set " + value + " where " + condition;
            if(this._conn.ExcuteNonQuery(query)>0)
            {
                return true;
            }
            return false;   
        }
        public bool UpdateWebUser(long user_id,string fieldname,string value)
        {
            value = fieldname + "='" + value + "'";
            string condition = WebUserField.UserId.ToString() + "=" + user_id;
            string query = @"update " + WebUser.TableName + " set " + value + " where " + condition;
            if (this._conn.ExcuteNonQuery(query) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

    }
}
